﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

/* OpenXml */
using System.IO;
using System.Xml;
using OfficeOpenXml;
using System.Drawing;
using OfficeOpenXml.Style;
/* OpenXml */

using KPIS.GERP.GFMS.IBLL;
using KPIS.GERP.GFMS.BLL;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.APP.App_UI.Commons;
using System.Globalization;
using System.Data;
using PdfSharp.Pdf;
using PdfSharp.Pdf.AcroForms;
using PdfSharp.Pdf.IO;
using PdfSharp.Drawing;
using KPIS.GERP.GFMS.DAL;
using KPIS.GERP.GFMS.APP.Reports;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using KPIS.DBM;
using KPIS.GERP.ESS.BLL;

namespace KPIS.GERP.GFMS.APP.App_UI.Forms
{
    public partial class PopupPrintExcel : System.Web.UI.Page
    {
        Main main = new Main();
        MailMerge mailMerge = new MailMerge();
        IBLL_FIM_Payments pay = new B_FIM_Payments();
        IBLL_FIM_Receives recv = new B_FIM_Receives();
        IBLL_FIM_PaymentCheques cheque = new B_FIM_PaymentCheques();
        IBLL_FIM_Cheques cheque2 = new B_FIM_Cheques();
        IBLL_HRM_PS_Employees emp = new B_HRM_PS_Employees();
        IBLL_FIM_MAS_Contacts cont = new B_FIM_MAS_Contacts();
        IBLL_FIM_MAS_Banks ban = new B_FIM_MAS_Banks();
        IBLL_FIM_MoneyReturns re = new B_FIM_MoneyReturns();
        IBLL_FIM_MoneyRequests mr = new B_FIM_MoneyRequests();
        IBLL_ACM_GENERATE_ACCOUNT_Auto acm = new B_ACM_GENERATE_ACCOUNT_Auto();
        IBLL_FIM_Transfers tran = new B_FIM_Transfers();
        D_FIM_SelectReports report = new D_FIM_SelectReports();
        static string path; static string refType;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string strSeq = Request.QueryString["Seq"];
                string strType = Request.QueryString["Type"];

                string strMonth = Request.QueryString["Month"];
                string strYear = Request.QueryString["Year"];

                if (String.IsNullOrEmpty(strType)) return;

                ExecuteData(strSeq, strMonth, strYear, ref strType);
            }
        }

        private string GetNameCheque(IList<M_FIM_PaymentCheques> pchq)
        {
            string ret = string.Empty;
            List<M_FIM_Payments> plist = new List<M_FIM_Payments>();
            foreach (M_FIM_PaymentCheques item in pchq)
            {
                M_FIM_Payments pm = new M_FIM_Payments();
                pm = pay.GetInfo(item.pay_seq.ToString());
                plist.Add(pm);
            }
            ret = plist.FirstOrDefault().payable_name;
            return ret;
        }

        private string GetEmpName(string seq)
        {
            var m = emp.GetInfo(seq);            
            return m.emp_name ?? "";
        }

        private M_HRM_PS_Employees GetEmpInfo(string seq)
        {
            var m = emp.GetInfo(seq);
            return m;
        }

        private M_HRM_PS_Employees GetEmpAddrInfo(string seq)
        {
            var m = emp.GetAddressInfo(seq);
            return m;
        }

        private M_FIM_MAS_Contacts GetContactInfo(string seq)
        {
            var m = cont.GetInfo(seq);
            return m;
        }

        private M_FIM_MAS_Contacts GetContactAddrInfo(string seq)
        {
            var m = cont.GetAddressInfo(seq);
            return m;
        }

        #region[PV RV]

        private void ExecuteData(string seq, string month, string year, ref string type)
        {
            Dictionary<string, string> _dtMain; 
            Dictionary<string, string> _dtAccH; Dictionary<string, string> _dtAccD;
            switch (type)
            {
                case "PV":
                    var pvModel = pay.GetInfo(seq);
                    if (pvModel.method_seq != null)
                    {
                        switch (pvModel.method_seq)
                        {
                            case 1: type = "PC"; refType = "fim_payments"; break;
                            case 2: type = "PQ"; refType = "fim_payments"; break;
                            case 3: type = "PT"; refType = "fim_payments"; break;
                            case 4: type = "PP"; refType = "fim_payments"; break;
                        }                        
                    }

                    if (pvModel.event_seq != 19)
                    {
                        _dtMain = GetDataPV(pvModel, type);
                        _dtAccH = GetDataAccHeader(seq, refType);
                        _dtAccD = GetDataAccDetail(seq, refType);
                        WriteDicToExcel(_dtMain, _dtAccH, _dtAccD, type);
                    }
                    else
                    {
                        var cp = cheque.GetFull(seq, null);
                        if (cp != null && cp.Count > 0)
                        {
                            var tf = tran.GetFullFromCheque(cp[0].cheque_seq.ToString());

                            if (tf != null && tf.Count > 0)
                            {
                                _dtMain = GetDataPV(pvModel, type);
                                _dtAccH = GetDataAccHeader(tf[0].transfer_seq.ToString(), "fim_transfers");
                                _dtAccD = GetDataAccDetail(tf[0].transfer_seq.ToString(), "fim_transfers");
                                WriteDicToExcel(_dtMain, _dtAccH, _dtAccD, type);
                                return;
                            }
                        }

                        main.MessageAlert(Page, "ข้อมูลไม่ถูกต้อง กรุณาตรวจสอบรายการอีกครั้ง");
                    }
                    break;

                case "RV":
                    type = "RV";refType = "fim_receives";
                    var rvModel = new M_FIM_Receives();
                    rvModel = recv.GetInfo(seq);
                    _dtMain = GetDataRV(rvModel, type);
                    _dtAccH = GetDataAccHeader(seq, refType);
                    _dtAccD = GetDataAccDetail(seq, refType);
                    WriteDicToExcel(_dtMain, _dtAccH, _dtAccD, type);
                    
                    break;

                case "RE":
                    type = "RE"; refType = "fim_receives";
                    var blModel = new M_FIM_Receives();
                    blModel = recv.GetInfo(seq);
                    _dtMain = GetDataRV(blModel, type);
                    //_dtAccH = GetDataAccHeader(seq, refType);
                    //_dtAccD = GetDataAccDetail(seq, refType);
                    WriteDicToExcel(_dtMain, new Dictionary<string, string>(), new Dictionary<string, string>(), type);

                    break;

                case "RT":
                    type = "RT"; refType = "fim_money_returns";
                    var rtModel = new M_FIM_MoneyReturns();
                    rtModel = re.GetInfo(seq);
                    _dtMain = GetDataRT(rtModel, type);
                    _dtAccH = GetDataAccHeader(seq, refType);
                    _dtAccD = GetDataAccDetail(seq, refType);
                    WriteDicToExcel(_dtMain, _dtAccH, _dtAccD, type);

                    break;

                case "RT2":
                    if (!string.IsNullOrEmpty(seq))
                    {
                        double? dReqAmount = mr.GetInfo(seq).total_amount;
                        DataTable dtbReturn = re.GetReturnsFromMoneyReqSeq(Convert.ToInt32(seq));
                        Dictionary<string, string> fields = new Dictionary<string, string>();
                        fields.Add("F3", "@n" + (dReqAmount ?? 0).ToString());

                        for (int i = 0; i < dtbReturn.Rows.Count; i++)
                        {
                            string strIndex = (i + 3).ToString();

                            fields.Add("A" + strIndex, (i + 1).ToString());
                            fields.Add("B" + strIndex, main.ConvertNullDateTime(dtbReturn.Rows[i]["report_date"]).HasValue ? main.ConvertNullDateTime(dtbReturn.Rows[i]["report_date"]).Value.ToShortDateString() : "");
                            fields.Add("C" + strIndex, dtbReturn.Rows[i]["ref_no"].ToString());
                            fields.Add("D" + strIndex, (main.ConvertNullDouble(dtbReturn.Rows[i]["amount"]) ?? 0).ToString("#,##0.00"));
                        }

                        string strTemplatePath = Server.MapPath("~/Reports/Templates/รายงานการส่งใช้เงินยืม.xlsx");

                        mailMerge.MergeExcel(Response, strTemplatePath, string.Format("รายงานการส่งใช้เงินยืม-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, fields);
                    }

                    break;

                case "CHQ":
                    type = "CHQ";refType = "fim_payment_cheques";
                    _dtMain = GetDataCHQ(seq, type);
                    WriteDicToExcel(_dtMain, new Dictionary<string, string>(), new Dictionary<string, string>(), type);

                    break;

                case "PND":
                    type = "PND";refType = "fim_payments";
                    var pndModel = pay.GetInfo(seq);
                    _dtMain = GetDataPND(pndModel, type);
                    //postFimTranfers(pndModel.pay_seq.ToString(), pndModel.bgm_source_seq.ToString(), pndModel.bgm_year.ToString(), pndModel.record_status);
                    WriteDicToExcel(_dtMain, new Dictionary<string, string>(), new Dictionary<string, string>(), type);
                    
                    break;

                case "PP30":
                    GetPP30(seq);
                    break;
                case "PND2":
                case "PNDDtls":
                    GetPND(seq);
                    break;
                case "Salary":
                    GetSalary(month, year);
                    break;

                default: break;
            }

        }

        private Dictionary<string, string> GetDataPND(M_FIM_Payments cModel, string type)
        {
            Dictionary<string, string> dt = new Dictionary<string, string>();
                        
            int n = 0;
            if (type.Equals("PND"))
            {
                M_FIM_PaymentRecordPrints prp = new M_FIM_PaymentRecordPrints();
                dt = new Dictionary<string, string>();
                n = 1;
                dt.Add(n.ToString() + ":" + n.ToString(), cModel.pay_seq.ToString());
                dt.Add(n.ToString() + ":" + (n + 1).ToString(), cModel.ref_no);
                dt.Add(n.ToString() + ":" + (n + 2).ToString(), cModel.payable_name);

                string tax_id = string.Empty;
                string pnd_type = string.Empty;
                string addr = string.Empty;

                if (cModel.contact_seq.HasValue)
                {
                    var m = GetContactAddrInfo(cModel.contact_seq.Value.ToString());
                    addr = m.address;
                    tax_id = m.tax_id;
                    if (String.IsNullOrEmpty(m.contact_type_group_name))
                        prp.doc_type = "PND3";
                    else
                        prp.doc_type = m.contact_type_group_name.Equals("บุคคล") ? "PND3" : "PND53";
                }
                else
                {
                    prp.doc_type = "PND";
                }

                dt.Add(n.ToString() + ":" + (n + 3).ToString(), prp.doc_type.Equals("PND3") ? "TRUE" : "FALSE");
                dt.Add(n.ToString() + ":" + (n + 4).ToString(), prp.doc_type.Equals("PND53") ? "TRUE" : "FALSE");
                dt.Add(n.ToString() + ":" + (n + 5).ToString(), tax_id);
                dt.Add(n.ToString() + ":" + (n + 6).ToString(), addr);
                dt.Add(n.ToString() + ":" + (n + 7).ToString(), cModel.pay_name);
                dt.Add(n.ToString() + ":" + (n + 8).ToString(), cModel.pay_date.HasValue?cModel.pay_date.Value.ToString("dd/MM/yyyy"):"");
                //dt.Add(n.ToString() + ":" + (n + 9).ToString(), cModel.real_pay_date.HasValue ? cModel.real_pay_date.Value.ToString("dd/MM/yyyy") : "");
                dt.Add(n.ToString() + ":" + (n + 9).ToString(), cModel.real_pay_date.HasValue ? cModel.real_pay_date.Value.ToString("dd/MM/yyyy") : "-");
                double? amt = 0; double? tax = 0;
                if (cModel.amount.HasValue)
                    amt = cModel.amount.Value;
                if (cModel.tax.HasValue)
                    tax = cModel.tax.Value;
                //Modified on 27/5/57
                //dt.Add(n.ToString() + ":" + (n + 10).ToString(), (tax.Value == 0?amt.Value.ToString("#,##0.00"):(amt.Value-tax.Value).ToString("#,##0.00")));
                M_FIM_MoneyRequests mrModel = new M_FIM_MoneyRequests();
                if (cModel.money_req_seq.HasValue)
                    mrModel = GetMoneyRequestInfo(cModel.money_req_seq);

                dt.Add(n.ToString() + ":" + (n + 10).ToString(), ((amt ?? 0) + (tax ?? 0) - (cModel.vat ?? 0)).ToString("#,##0.00"));//(cModel.vat_rate == 0 ? (amt.Value + tax.Value).ToString("#,##0.00") : (mrModel.total_amount.Value - cModel.vat.Value).ToString("#,##0.00")));
                dt.Add(n.ToString() + ":" + (n + 11).ToString(), (tax ?? 0).ToString("#,##0.00"));               
                dt.Add(n.ToString() + ":" + (n + 12).ToString(), mrModel.bgm_proj_oper_code);

                prp = GetDocNo(prp);
                
                dt.Add(n.ToString() + ":" + (n + 13).ToString(), String.Format("{0:0000000}", prp.doc_no));
                dt.Add(n.ToString() + ":" + (n + 14).ToString(), String.Format("{0:0000}", prp.book_no));

                //Assignment Value                
                //prp.book_no = docModel.book_no;
                prp.created_by = main.ConvertNullInt(Session["EmpSeq"]);
                prp.created_when = DateTime.Now;
                //prp.doc_no = docModel.doc_no;
                //prp.doc_type = pnd_type.ToLower();
                prp.print_flag = 1;
                prp.record_status = "W";
                prp.ref_id = cModel.pay_seq;
                prp.ref_no = cModel.ref_no;
                InsertPrintRecord(prp);
            }
            
            return dt;
            //throw new NotImplementedException();
        }

        private M_FIM_PaymentRecordPrints GetDocNo(M_FIM_PaymentRecordPrints model) {
            var docModel = pay.GetNextDocNoPND(model.doc_type);
            docModel.doc_type = model.doc_type;
            if (docModel.doc_no > 9999999)
            {
                docModel.book_no += 1;
                docModel.doc_no = 1;
            }
            else if (docModel.book_no == 0 || docModel.doc_no == 0)
            {
                docModel.book_no = 1;
                docModel.doc_no = 1;
            }
            else
            {
                docModel.doc_no += 1;
            }
            return docModel;
        }
        
        private void InsertPrintRecord(M_FIM_PaymentRecordPrints prp)
        {
            pay.InsertPNDPrint(prp);
        }

        private Dictionary<string, string> GetDataCHQ(string seq, string type)
        {
            Dictionary<string, string> dt = new Dictionary<string, string>();
            int n = 0;
            if (type.Equals("CHQ"))
            {
                var cModel = cheque.GetFull(null, seq);
                if (cModel.Count > 0)
                {
                    dt = new Dictionary<string, string>();
                    n = 1;
                    var model = cModel.FirstOrDefault();
                    dt.Add(n.ToString() + ":" + n.ToString(), model.cheque_no.HasValue ?
                          model.cheque_no.Value.ToString() : "");
                    dt.Add(n.ToString() + ":" + (n + 1).ToString(), GetNameCheque(cModel));
                    dt.Add(n.ToString() + ":" + (n + 2).ToString(), cModel.Sum(c => c.amount).Value.ToString());

                    var ChequeModel = cheque2.GetInfo(seq);
                    DateTime? dtChequeDate = ChequeModel.cheque_date;
                    string strCheque = null;
                    string d1 = null, d2 = null;
                    string M1 = null, M2 = null;
                    string y1= null, y2= null , y3= null, y4= null;
                    if (dtChequeDate != null)
                    {
                        strCheque = Convert.ToString(dtChequeDate);

                    }
                    else if (dtChequeDate == null) // ไม่มีวันที่สั่งจ่าย ให้สั่งจ่ายเช็คก่อนถ้าต้องการจะปริ้นท์ ไม่งั้น กดปุ่มปริ้นท์เช็ค จะไม่มีปริ้นท์
                    {
                       d1 = string.Empty; d2 = string.Empty;
                       M1 = string.Empty; M2 = string.Empty;
                       y1 = string.Empty; y2 = string.Empty;
                       y3 = string.Empty; y4 = string.Empty;
                    }
                    string[] spiltChequeDate = (strCheque??"").Split('/');
                    //string d1 = string.Empty;string d2 = string.Empty;
                    //string M1 = string.Empty;string M2 = string.Empty;
                    //string y1 = string.Empty;string y2 = string.Empty;
                    //string y3 = string.Empty;string y4 = string.Empty;

                    string Date = spiltChequeDate[0];
                    string Month = spiltChequeDate.Length >= 2 ? spiltChequeDate[1] : "";
                    string year = spiltChequeDate.Length >= 3 ? spiltChequeDate[2] : "";

                    if (Date.Length == 2)
                    {
                        d1 = Date.Substring(0,1);
                        d2 = Date.Substring(1);
                    }
                    else if(Date.Length == 1)
                    {
                        d1 = "0";
                        d2 = Date.Substring(0,1);
                    }

                    if (Month.Length == 2)
                    {
                        M1 = Month.Substring(0, 1);
                        M2 = Month.Substring(1);
                    }
                    else if (Month.Length == 1)
                    {
                        M1 = "0";
                        M2 = Month.Substring(0,1);
                    }

                     string[] splitYear = year.Split(' ');
                    try
                    {
                        y1 = splitYear[0].Substring(0, 1);
                        y2 = splitYear[0].Substring(1, 1);
                        y3 = splitYear[0].Substring(2, 1);
                        y4 = splitYear[0].Substring(3);
                    } catch (Exception ex){}


                    dt.Add(n.ToString() + ":" + (n + 3).ToString(), d1);
                    dt.Add(n.ToString() + ":" + (n + 4).ToString(), d2);
                    dt.Add(n.ToString() + ":" + (n + 5).ToString(), M1);
                    dt.Add(n.ToString() + ":" + (n + 6).ToString(), M2);
                    dt.Add(n.ToString() + ":" + (n + 7).ToString(), y1);
                    dt.Add(n.ToString() + ":" + (n + 8).ToString(), y2);
                    dt.Add(n.ToString() + ":" + (n + 9).ToString(), y3);
                    dt.Add(n.ToString() + ":" + (n + 10).ToString(), y4);
                }
            }            
            return dt;
        }

        private Dictionary<string, string> GetDataPV(M_FIM_Payments vModel, string type)
        {
            Dictionary<string, string> dt = new Dictionary<string, string>();
            int n = 0;
            if (type.Equals("PC"))
            {   
                dt = new Dictionary<string, string>();
                n = 1;
                dt.Add(n.ToString() + ":" + n.ToString(), vModel.pay_seq.ToString());
                dt.Add(n.ToString() + ":" + (n + 1).ToString(), !String.IsNullOrEmpty(vModel.ref_no) ? vModel.ref_no : "");                
                dt.Add(n.ToString() + ":" + (n + 2).ToString(), vModel.real_pay_date.HasValue ?
                    vModel.real_pay_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                dt.Add(n.ToString() + ":" + (n + 3).ToString(), !String.IsNullOrEmpty(vModel.pay_name) ? vModel.pay_name : "");
                dt.Add(n.ToString() + ":" + (n + 4).ToString(), !String.IsNullOrEmpty(vModel.emp_name) ? vModel.emp_name : "");
                dt.Add(n.ToString() + ":" + (n + 5).ToString(),
                    vModel.penalty_amount.HasValue ? vModel.penalty_amount.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 6).ToString(), vModel.tax.HasValue ? vModel.tax.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 7).ToString(), vModel.amount.HasValue ? vModel.amount.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.payable_name);
                dt.Add(n.ToString() + ":" + (n + 9).ToString(), Session["UserName"]!=null?Session["UserName"].ToString():"");
                M_FIM_MoneyRequests mrModel = new M_FIM_MoneyRequests();
                if(vModel.money_req_seq.HasValue)
                    mrModel = GetMoneyRequestInfo(vModel.money_req_seq);
                dt.Add(n.ToString() + ":" + (n + 10).ToString(), mrModel.bgm_proj_oper_code);
                dt.Add(n.ToString() + ":" + (n + 14).ToString(), DateTime.Now.ToString());
                //dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.contact_seq.HasValue ? vModel.contact_name.ToString() : (vModel.emp_name ?? ""));
            }
            else if (type.Equals("PQ"))
            {
                var cModel = cheque.GetFull(vModel.pay_seq.ToString(),null);
                if (cModel.Count > 0)
                {
                    dt = new Dictionary<string, string>();
                    n = 1;
                    dt.Add(n.ToString() + ":" + n.ToString(), vModel.pay_seq.ToString());
                    dt.Add(n.ToString() + ":" + (n + 1).ToString(), !String.IsNullOrEmpty(vModel.ref_no) ? vModel.ref_no: "");
                    dt.Add(n.ToString() + ":" + (n + 2).ToString(), vModel.real_pay_date.HasValue ?
                        vModel.real_pay_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                    dt.Add(n.ToString() + ":" + (n + 3).ToString(), !String.IsNullOrEmpty(vModel.pay_name) ? vModel.pay_name : "");
                    dt.Add(n.ToString() + ":" + (n + 4).ToString(), !String.IsNullOrEmpty(vModel.emp_name) ? vModel.emp_name : "");
                    //dt.Add(n.ToString() + ":" + (n + 4).ToString(), !String.IsNullOrEmpty(vModel.payable_name) ? vModel.payable_name : "");
                    M_FIM_MAS_Banks m = ban.GetBankNamePaymentCheques((int?)vModel.pay_seq);
                    dt.Add(n.ToString() + ":" + (n + 5).ToString(), m != null ? (!String.IsNullOrEmpty(m.bank_name) ? m.bank_name : "") : "");
                    dt.Add(n.ToString() + ":" + (n + 6).ToString(), 
                        cModel.Select(p =>
                                            {
                                                object value = p.cheque_no;
                                                return value == null ? "" : value.ToString();
                                            }).FirstOrDefault()
                                            );
                    dt.Add(n.ToString() + ":" + (n + 7).ToString(), 
                        cModel.Select(p =>
                                            {
                                                object value = p.amount;
                                                return value == null ? "" : value.ToString();
                                            }).FirstOrDefault()
                                            );
                    //dt.Add(n.ToString() + ":" + (n + 6).ToString(), cModel.cheque_no.HasValue ? cModel.cheque_no.ToString() : "");
                    //dt.Add(n.ToString() + ":" + (n + 7).ToString(), cModel.amount.HasValue ? cModel.amount.ToString() : "");
                    //dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.contact_seq.HasValue ? vModel.contact_name.ToString() : (vModel.emp_name ?? ""));
                    dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.payable_name);
                    dt.Add(n.ToString() + ":" + (n + 9).ToString(), !String.IsNullOrEmpty(vModel.event_name) ? vModel.event_name : "");
                    dt.Add(n.ToString() + ":" + (n + 10).ToString(), 
                        cModel.Select(p =>
                                            {
                                                object value = p.bank_acc_name;
                                                return value == null ? "" : value.ToString();
                                            }).FirstOrDefault()
                                            );
                    dt.Add(n.ToString() + ":" + (n + 11).ToString(), 
                        cModel.Select(p =>
                                            {
                                                object value = p.bank_acc_no;
                                                return value == null ? "" : value.ToString();
                                            }).FirstOrDefault()
                                            );
                    dt.Add(n.ToString() + ":" + (n + 12).ToString(), Session["UserName"] != null ? Session["UserName"].ToString() : "");
                    M_FIM_MoneyRequests mrModel = new M_FIM_MoneyRequests();
                    if (vModel.money_req_seq.HasValue)
                        mrModel = GetMoneyRequestInfo(vModel.money_req_seq);
                    dt.Add(n.ToString() + ":" + (n + 13).ToString(), mrModel.bgm_proj_oper_code);
                    dt.Add(n.ToString() + ":" + (n + 14).ToString(), DateTime.Now.ToString());   
                   
                }
            }
            else if (type.Equals("PT")) {
                dt = new Dictionary<string, string>();
                n = 1;
                dt.Add(n.ToString() + ":" + n.ToString(), vModel.pay_seq.ToString());
                dt.Add(n.ToString() + ":" + (n + 1).ToString(), !String.IsNullOrEmpty(vModel.ref_no) ? vModel.ref_no : "");
                dt.Add(n.ToString() + ":" + (n + 2).ToString(), vModel.real_pay_date.HasValue ?
                    vModel.real_pay_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                dt.Add(n.ToString() + ":" + (n + 3).ToString(), !String.IsNullOrEmpty(vModel.pay_name) ? vModel.pay_name : "");
                //dt.Add(n.ToString() + ":" + (n + 4).ToString(), !String.IsNullOrEmpty(vModel.payable_name) ? vModel.payable_name : "");
                dt.Add(n.ToString() + ":" + (n + 4).ToString(), !String.IsNullOrEmpty(vModel.emp_name) ? vModel.emp_name : "");
                dt.Add(n.ToString() + ":" + (n + 5).ToString(),
                    vModel.penalty_amount.HasValue ? vModel.penalty_amount.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 6).ToString(), vModel.tax.HasValue ? vModel.tax.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 7).ToString(), vModel.amount.HasValue ? vModel.amount.ToString() : "");
                //dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.contact_seq.HasValue ? vModel.contact_name.ToString() : (vModel.emp_name ?? ""));
                dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.payable_name);
                dt.Add(n.ToString() + ":" + (n + 9).ToString(), Session["UserName"] != null ? Session["UserName"].ToString() : "");
                M_FIM_MoneyRequests mrModel = new M_FIM_MoneyRequests();
                if (vModel.money_req_seq.HasValue)
                    mrModel = GetMoneyRequestInfo(vModel.money_req_seq);
                dt.Add(n.ToString() + ":" + (n + 10).ToString(), mrModel.bgm_proj_oper_code);
                dt.Add(n.ToString() + ":" + (n + 14).ToString(), DateTime.Now.ToString());
            }
            else if (type.Equals("PP"))
            {
                dt = new Dictionary<string, string>();
                n = 1;
                dt.Add(n.ToString() + ":" + n.ToString(), vModel.pay_seq.ToString());
                dt.Add(n.ToString() + ":" + (n + 1).ToString(), !String.IsNullOrEmpty(vModel.ref_no) ? vModel.ref_no : "");
                dt.Add(n.ToString() + ":" + (n + 2).ToString(), vModel.real_pay_date.HasValue ?
                    vModel.real_pay_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                dt.Add(n.ToString() + ":" + (n + 3).ToString(), !String.IsNullOrEmpty(vModel.pay_name) ? vModel.pay_name : "");
                dt.Add(n.ToString() + ":" + (n + 4).ToString(), !String.IsNullOrEmpty(vModel.emp_name) ? vModel.emp_name : "");
                //dt.Add(n.ToString() + ":" + (n + 4).ToString(), !String.IsNullOrEmpty(vModel.payable_name) ? vModel.payable_name : "");
                dt.Add(n.ToString() + ":" + (n + 5).ToString(),
                    vModel.penalty_amount.HasValue ? vModel.penalty_amount.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 6).ToString(), vModel.tax.HasValue ? vModel.tax.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 7).ToString(), vModel.amount.HasValue ? vModel.amount.ToString() : "");
                //dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.contact_seq.HasValue ? vModel.contact_name.ToString() : (vModel.emp_name ?? ""));
                dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.payable_name);
                dt.Add(n.ToString() + ":" + (n + 9).ToString(), Session["UserName"] != null ? Session["UserName"].ToString() : "");
                M_FIM_MoneyRequests mrModel = new M_FIM_MoneyRequests();
                if (vModel.money_req_seq.HasValue)
                    mrModel = GetMoneyRequestInfo(vModel.money_req_seq);
                dt.Add(n.ToString() + ":" + (n + 10).ToString(), mrModel.bgm_proj_oper_code);
                dt.Add(n.ToString() + ":" + (n + 14).ToString(), DateTime.Now.ToString());
            }
            return dt;
        }

        private M_FIM_MoneyRequests GetMoneyRequestInfo(int? seq)
        {
            return mr.GetInfo(seq.ToString());
        }

        private Dictionary<string, string> GetDataAccHeader(string seq, string refType)
        {
            Dictionary<string, string> dt = new Dictionary<string, string>();
            var hModel = acm.GetListMaster(seq, refType);
            if (hModel.Count > 0) {
                int r = 0;
                int c = 1;
                foreach (var item in hModel)
                {
                    r += 1;
                    dt.Add(r.ToString() + ":" + c.ToString(), !String.IsNullOrEmpty(item.ref_type) ? item.ref_type: "");
                    dt.Add(r.ToString() + ":" + (c + 1).ToString(), !String.IsNullOrEmpty(item.tran_type) ? item.tran_type : "");
                    dt.Add(r.ToString() + ":" + (c + 2).ToString(), !String.IsNullOrEmpty(item.ref_no) ? item.ref_no: "");
                    dt.Add(r.ToString() + ":" + (c + 3).ToString(), item.tran_date.HasValue ? 
                        item.tran_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                    dt.Add(r.ToString() + ":" + (c + 4).ToString(), !String.IsNullOrEmpty(item.tran_name) ?item.tran_name:"");
                    dt.Add(r.ToString() + ":" + (c + 5).ToString(), item.ref_seq.HasValue ? item.ref_seq.ToString() : "");
                    dt.Add(r.ToString() + ":" + (c + 6).ToString(), item.bgm_year.HasValue ? item.bgm_year.ToString() : "");
                }
            }

            return dt;
        }

        private Dictionary<string, string> GetDataAccDetail(string seq, string refType)
        {
            Dictionary<string, string> dt = new Dictionary<string, string>();
            var dModel = acm.GetListDetail(main.ConvertNullInt(seq), refType);
            int r = 0;
            int c = 1;

            if (refType == "fim_payments")
            {
                var payModel = pay.GetInfo(seq);

                if (payModel.money_req_seq.HasValue && payModel.event_seq != 16)
                {
                    var reqModel = mr.GetInfo(payModel.money_req_seq.ToString());

                    if (reqModel.icm_dn_seq.HasValue)
                    {
                        var dnModel = GetDnInfo(reqModel.icm_dn_seq.Value);

                        r++;
                        dt.Add(r.ToString() + ":" + (c + 2).ToString(), !String.IsNullOrEmpty(dnModel.icm_dn_ref_no) ? dnModel.icm_dn_ref_no : "");
                        dt.Add(r.ToString() + ":" + (c + 1).ToString(), dnModel.receive_date.HasValue ? dnModel.receive_date.Value.ToShortDateString() : "");

                        var dModel3 = acm.GetListDetail(reqModel.icm_dn_seq, "icm_delivery_note");
                        if (dModel3.Count > 0)
                        {
                            var a = dModel3.GroupBy(m => new
                                {
                                    account_code = m.account_code,
                                    account_name = m.account_name,
                                    dr_or_cr = m.dr_or_cr,

                                }).Select(m => new
                                {
                                    account_code = m.Key.account_code,
                                    account_name = m.Key.account_name,
                                    dr_or_cr = m.Key.dr_or_cr,
                                    amount_dr = m.Sum(g => g.amount_dr),
                                    amount_cr = m.Sum(g => g.amount_cr),
                                });
                            foreach (var item in a)
                            {
                                r += 1;
                                //dt.Add(r.ToString() + ":" + c.ToString(), item.account_seq.HasValue ? item.account_seq.ToString() : "");
                                dt.Add(r.ToString() + ":" + c.ToString(), r.ToString());
                                dt.Add(r.ToString() + ":" + (c + 1).ToString(), !String.IsNullOrEmpty(item.account_code) ? item.account_code : "");
                                dt.Add(r.ToString() + ":" + (c + 2).ToString(), !String.IsNullOrEmpty(item.account_name) ? item.account_name : "");
                                dt.Add(r.ToString() + ":" + (c + 3).ToString(), !String.IsNullOrEmpty(item.dr_or_cr) ? item.dr_or_cr : "");
                                dt.Add(r.ToString() + ":" + (c + 4).ToString(), item.amount_dr.HasValue ? item.amount_dr.ToString() : "");
                                dt.Add(r.ToString() + ":" + (c + 5).ToString(), item.amount_cr.HasValue ? item.amount_cr.ToString() : "");
                            }

                            r++;
                        }
                    }

                    var dModel2 = acm.GetListDetail(payModel.money_req_seq, "fim_money_requests");

                    r++;
                    dt.Add(r.ToString() + ":" + (c + 2).ToString(), !String.IsNullOrEmpty(reqModel.ref_no) ? reqModel.ref_no : "");
                    dt.Add(r.ToString() + ":" + (c + 1).ToString(), reqModel.money_req_date.HasValue ? reqModel.money_req_date.Value.ToShortDateString() : "");

                    if (dModel2.Count > 0)
                    {
                        foreach (var item in dModel2)
                        {
                            r += 1;
                            //dt.Add(r.ToString() + ":" + c.ToString(), item.account_seq.HasValue ? item.account_seq.ToString() : "");
                            dt.Add(r.ToString() + ":" + c.ToString(), r.ToString());
                            dt.Add(r.ToString() + ":" + (c + 1).ToString(), !String.IsNullOrEmpty(item.account_code) ? item.account_code : "");
                            dt.Add(r.ToString() + ":" + (c + 2).ToString(), !String.IsNullOrEmpty(item.account_name) ? item.account_name : "");
                            dt.Add(r.ToString() + ":" + (c + 3).ToString(), !String.IsNullOrEmpty(item.dr_or_cr) ? item.dr_or_cr : "");
                            dt.Add(r.ToString() + ":" + (c + 4).ToString(), item.amount_dr.HasValue ? item.amount_dr.ToString() : "");
                            dt.Add(r.ToString() + ":" + (c + 5).ToString(), item.amount_cr.HasValue ? item.amount_cr.ToString() : "");
                        }

                        r++;
                    }
                }

                r++;
                dt.Add(r.ToString() + ":" + (c + 2).ToString(), !String.IsNullOrEmpty(payModel.ref_no) ? payModel.ref_no : "");
                dt.Add(r.ToString() + ":" + (c + 1).ToString(), payModel.real_pay_date.HasValue ? payModel.real_pay_date.Value.ToShortDateString() : "");
            }
            else if (refType == "fim_transfers")
            {
                var tf = tran.GetInfo(seq);
                if (tf != null && tf.transfer_seq > 0)
                {
                    r++;
                    dt.Add(r.ToString() + ":" + (c + 2).ToString(), !String.IsNullOrEmpty(tf.ref_no) ? tf.ref_no : "");
                    dt.Add(r.ToString() + ":" + (c + 1).ToString(), tf.transfer_date.HasValue ? tf.transfer_date.Value.ToShortDateString() : "");
                }
            }

            if (dModel.Count > 0)
            {
                foreach (var item in dModel)
                {
                    r += 1;
                    //dt.Add(r.ToString() + ":" + c.ToString(), item.account_seq.HasValue ? item.account_seq.ToString() : "");
                    dt.Add(r.ToString() + ":" + c.ToString(), r.ToString());
                    dt.Add(r.ToString() + ":" + (c + 1).ToString(), !String.IsNullOrEmpty(item.account_code) ? item.account_code : "");
                    dt.Add(r.ToString() + ":" + (c + 2).ToString(), !String.IsNullOrEmpty(item.account_name) ? item.account_name : "");
                    dt.Add(r.ToString() + ":" + (c + 3).ToString(), !String.IsNullOrEmpty(item.dr_or_cr) ? item.dr_or_cr : "");
                    dt.Add(r.ToString() + ":" + (c + 4).ToString(), item.amount_dr.HasValue ? item.amount_dr.ToString() : "");
                    dt.Add(r.ToString() + ":" + (c + 5).ToString(), item.amount_cr.HasValue ? item.amount_cr.ToString() : "");
                }
            }

            return dt;

            #region [comment code]
            //    ////detail
            //    _arr[10] = dModel.Select(x => x.account_name).Where(Predicates.IsNotNull).FirstOrDefault();
            //    //lst.Add(dModel.Select(x => x.account_name).Where(Predicates.IsNotNull).FirstOrDefault());
            //    _arr[11] = dModel.Select(x => x.account_code).Where(Predicates.IsNotNull).FirstOrDefault();//dModel.Select(d => d.amount_dr).Where(d => d.Value != null).FirstOrDefault(d => d.);
            //    //lst.Add(dModel.Select(x => x.account_code).Where(Predicates.IsNotNull).FirstOrDefault());
            //    _arr[12] = dModel.Select(d => d.amount_dr).Sum(d => d.HasValue ? d.Value : 0).ToString();
            //    //lst.Add(dModel.Select(d => d.amount_dr).Sum(d => d.HasValue ? d.Value : 0).ToString());
            //    _arr[13] = dModel.Select(d => d.amount_cr).Sum(d => d.HasValue ? d.Value : 0).ToString();
            //    //lst.Add(dModel.Select(d => d.amount_cr).Sum(d => d.HasValue ? d.Value : 0).ToString());

            //    //detail
            //    _arr[10] = dModel.Select(x => x.account_name).Where(Predicates.IsNotNull).FirstOrDefault();
            //    _arr[11] = dModel.Select(x => x.account_code).Where(Predicates.IsNotNull).FirstOrDefault();//dModel.Select(d => d.amount_dr).Where(d => d.Value != null).FirstOrDefault(d => d.);
            //    _arr[12] = dModel.Select(d => d.amount_dr).Sum(d => d.HasValue ? d.Value : 0).ToString();
            //    _arr[13] = dModel.Select(d => d.amount_cr).Sum(d => d.HasValue ? d.Value : 0).ToString();
            //    _arr[13] = dModel.Select(d => d.amount_cr).Sum(d => d.HasValue ? d.Value : 0).ToString();
            #endregion [comment code]            
        }

        private Dictionary<string, string> GetDataRV(M_FIM_Receives vModel, string type)
        {
            Dictionary<string, string> dt = new Dictionary<string, string>();
            int n = 0;
            if (type.Equals("RV"))
            {
                dt = new Dictionary<string, string>();
                n = 1;
                dt.Add(n.ToString() + ":" + n.ToString(), vModel.receive_seq.ToString());
                dt.Add(n.ToString() + ":" + (n + 1).ToString(), !String.IsNullOrEmpty(vModel.ref_no) ? vModel.ref_no : "");
                dt.Add(n.ToString() + ":" + (n + 2).ToString(), vModel.receive_date.HasValue ?
                    vModel.receive_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                dt.Add(n.ToString() + ":" + (n + 3).ToString(), !String.IsNullOrEmpty(vModel.recive_remark) ? vModel.recive_remark : "");
                dt.Add(n.ToString() + ":" + (n + 4).ToString(), !String.IsNullOrEmpty(vModel.receive_type_name) ? vModel.receive_type_name : "");
                dt.Add(n.ToString() + ":" + (n + 5).ToString(), !String.IsNullOrEmpty(vModel.payer_name) ? vModel.payer_name: "");
                dt.Add(n.ToString() + ":" + (n + 6).ToString(), vModel.tax.HasValue ? vModel.tax.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 7).ToString(), vModel.amount.HasValue ? vModel.amount.ToString() : "");
                //dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.contact_seq.HasValue ? vModel.contact_name.ToString() : (vModel.emp_name ?? ""));
                dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.created_by.HasValue ? GetEmpName(vModel.created_by.ToString()) : "");
                
                dt.Add(n.ToString() + ":" + (n + 9).ToString(), Session["UserName"] != null ? Session["UserName"].ToString() : "");
                M_FIM_MoneyRequests mrModel = new M_FIM_MoneyRequests();
                if (vModel.money_req_seq.HasValue)
                    mrModel = GetMoneyRequestInfo(vModel.money_req_seq);
                dt.Add(n.ToString() + ":" + (n + 10).ToString(), mrModel.bgm_proj_oper_code);     
            }
            else if (type.Equals("RE"))
            {
                dt = new Dictionary<string, string>();
                n = 1;
                string addr = string.Empty;
                string cid = string.Empty;
                string cname = string.Empty;
                if (vModel.contact_seq.HasValue)
                {
                    var m = GetContactAddrInfo(vModel.contact_seq.Value.ToString());
                    addr = m.address;
                    cname = m.contact_name;
                }
                dt.Add(n.ToString() + ":" + n.ToString(), vModel.receive_seq.ToString());
                dt.Add(n.ToString() + ":" + (n + 1).ToString(), !String.IsNullOrEmpty(vModel.receipt_no) ? vModel.receipt_no : "");
                //dt.Add(n.ToString() + ":" + (n + 1).ToString(), vModel.vat_amount.HasValue ? vModel.vat_amount.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 2).ToString(), vModel.receive_date.HasValue ?
                    vModel.receive_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                dt.Add(n.ToString() + ":" + (n + 3).ToString(), !String.IsNullOrEmpty(n.ToString()) ? n.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 4).ToString(), !String.IsNullOrEmpty(vModel.receive_type_name) ? vModel.receive_type_name : "");
                dt.Add(n.ToString() + ":" + (n + 5).ToString(), !String.IsNullOrEmpty(vModel.payer_name) ? vModel.payer_name : "");
               
                dt.Add(n.ToString() + ":" + (n + 6).ToString(), !String.IsNullOrEmpty(addr) ? addr : "");
                //dt.Add(n.ToString() + ":" + (n + 6).ToString(), vModel.tax.HasValue ? vModel.tax.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 7).ToString(), vModel.exclude_vat.HasValue? vModel.exclude_vat.Value.ToString():(vModel.amount.HasValue ? vModel.amount.ToString() : ""));
                dt.Add(n.ToString() + ":" + (n + 8).ToString(), !String.IsNullOrEmpty(cname) ? cname : (vModel.emp_name ?? ""));
                string emp_name = string.Empty;
                string pos_name = string.Empty;
                if(vModel.created_by.HasValue){
                    var e = GetEmpInfo(vModel.created_by.Value.ToString());
                    emp_name = e.emp_name;
                    pos_name = e.pos_name;                    
                }
                dt.Add(n.ToString() + ":" + (n + 9).ToString(), emp_name);
                dt.Add(n.ToString() + ":" + (n + 10).ToString(), pos_name);
                dt.Add(n.ToString() + ":" + (n + 11).ToString(), vModel.created_when.HasValue ? vModel.created_when.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");

                dt.Add(n.ToString() + ":" + (n + 12).ToString(), (vModel.method_seq.HasValue && vModel.method_seq.Equals(1)) ? "TRUE" : "FALSE");
                dt.Add(n.ToString() + ":" + (n + 13).ToString(), (vModel.method_seq.HasValue && vModel.method_seq.Equals(3)) ? "TRUE" : "FALSE");
                dt.Add(n.ToString() + ":" + (n + 14).ToString(), (vModel.method_seq.HasValue && vModel.method_seq.Equals(2)) ? "TRUE" : "FALSE");
                if (vModel.method_seq.HasValue && vModel.method_seq.Equals(2))
                {                    
                    dt.Add(n.ToString() + ":" + (n + 15).ToString(), !String.IsNullOrEmpty(vModel.cheque_no) ? vModel.cheque_no : "");
                    dt.Add(n.ToString() + ":" + (n + 16).ToString(), !String.IsNullOrEmpty(vModel.bank_name) ? vModel.bank_name : "");
                    dt.Add(n.ToString() + ":" + (n + 17).ToString(), !String.IsNullOrEmpty(vModel.branch_name) ? vModel.branch_name : "");
                }
            }   
            return dt;          
        }

        private Dictionary<string, string> GetDataRT(M_FIM_MoneyReturns vModel, string type)
        {           
            Dictionary<string, string> dt = new Dictionary<string, string>();
            int n = 0;
            if (type.Equals("RT"))
            {
                dt = new Dictionary<string, string>();
                n = 1;
                dt.Add(n.ToString() + ":" + n.ToString(), vModel.return_seq.ToString());
                dt.Add(n.ToString() + ":" + (n + 1).ToString(), !String.IsNullOrEmpty(vModel.ref_no) ? vModel.ref_no : "");
                dt.Add(n.ToString() + ":" + (n + 2).ToString(), vModel.return_date.HasValue ?
                    vModel.return_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                dt.Add(n.ToString() + ":" + (n + 3).ToString(), !String.IsNullOrEmpty(vModel.return_name) ? vModel.return_name : "");
                dt.Add(n.ToString() + ":" + (n + 4).ToString(), !String.IsNullOrEmpty(vModel.money_req_name) ? vModel.money_req_name : "");
                dt.Add(n.ToString() + ":" + (n + 5).ToString(), !String.IsNullOrEmpty(vModel.debtor_name) ? vModel.debtor_name : "");
                dt.Add(n.ToString() + ":" + (n + 6).ToString(), vModel.money_req_total_bath_amount.HasValue ? vModel.money_req_total_bath_amount.ToString() : "");
                dt.Add(n.ToString() + ":" + (n + 7).ToString(), vModel.total_bath_amount.HasValue ? vModel.total_bath_amount.ToString() : "");
                ////dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.contact_seq.HasValue ? vModel.contact_name.ToString() : (vModel.emp_name ?? ""));
                //dt.Add(n.ToString() + ":" + (n + 8).ToString(), vModel.created_by.HasValue ? GetEmpName(vModel.created_by.ToString()) : "");
                dt.Add(n.ToString() + ":" + (n + 8).ToString(), Session["UserName"] != null ? Session["UserName"].ToString() : "");
                M_FIM_MoneyRequests mrModel = new M_FIM_MoneyRequests();
                if (vModel.money_req_seq.HasValue)
                    mrModel = GetMoneyRequestInfo(vModel.money_req_seq);
                dt.Add(n.ToString() + ":" + (n + 9).ToString(), mrModel.bgm_proj_oper_code);   
            }
            return dt;
        }

        private void WriteDicToExcel(Dictionary<string, string> _dtM, Dictionary<string, string> _dtH
            , Dictionary<string, string> _dtD, string type)
        {
            if (_dtM.Count <= 0) return;
            path = Server.MapPath("~/App_Data/FileTemplate");
            CheckFolderExist(path);

            FileInfo newFile = new FileInfo(Path.Combine(path, type + "Template.xlsx"));
            if (!newFile.Exists) return;

            using (ExcelPackage xlPackage = new ExcelPackage(newFile, true))
            {
                if (_dtM.Count > 0)
                    AddWorkSheet(_dtM, "M", ':', xlPackage);
                
                if (_dtH.Count > 0)
                    AddWorkSheet(_dtH, "H", ':', xlPackage);                    
               
                if (_dtD.Count > 0)
                    AddWorkSheet(_dtD, "D", ':', xlPackage);
                
                //xlPackage.Save(); //for save edit sheet
                //Byte[] bin = xlPackage.GetAsByteArray(); //for convert ByteArray                  
                //File.WriteAllBytes(file, bin); //for write ByteArray
                WriteFile(type, xlPackage);
            }
        }

        private static void AddWorkSheet(Dictionary<string, string> _dtM, string _sheet, char _char, ExcelPackage xlPackage)
        {          
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[_sheet];
            if (worksheet != null)
            {
                foreach (KeyValuePair<string, string> item in _dtM)
                {
                    string[] _cell = item.Key.Split(_char);
                    if (_cell.Length >= 2)
                    {
                        int row = Convert.ToInt32(_cell[0]);
                        int col = Convert.ToInt32(_cell[1]);
                        worksheet.Cells[row, col].Value = item.Value;
                        worksheet.Cells[row, col].Style.Font.SetFromFont(new Font("TH SarabunPSK", 14, FontStyle.Regular));
                        worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }
                }
            }
        }

        /*
        private void WriteArrToExcel(string[] _arr, string seq, string type)
        {
            if (_arr.Length <= 0) return;

            CheckFolderExist();
            string file = Path.Combine(path, type + "Template.xlsx");
            FileInfo newFile = new FileInfo(file);

            using (ExcelPackage xlPackage = new ExcelPackage(newFile, true))
            {
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Data");
                if (worksheet != null)
                {
                    for (int i = 0; i < _arr.Length; i++)
                    {
                        worksheet.Cells[i + 1, 1].Value = _arr[i];
                        worksheet.Cells[i + 1, 1].Style.Font.SetFromFont(new Font("AngsanaUPC", 14, FontStyle.Regular));
                        worksheet.Cells[i + 1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }
                }
                //xlPackage.Save();

                //Byte[] bin = xlPackage.GetAsByteArray();                  
                //File.WriteAllBytes(file, bin);
                WriteFile(type, xlPackage);
            }
        }
        */

        private void WriteFile(string type, ExcelPackage xlPackage)
        {
            string filename = type + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;  filename=" + filename);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.BinaryWrite(xlPackage.GetAsByteArray());
            Response.Flush();
            Response.End();
        }

        private void CheckFolderExist(string path)
        {
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
                main.FolderACL("Everyone", path);
            }
        }

        public static void PropertyValue(ExcelPackage xlPackage)
        {
            // set some core property values
            xlPackage.Workbook.Properties.Title = "Excel Report";
            xlPackage.Workbook.Properties.Author = "MarsTechnolofy's Staff";
            xlPackage.Workbook.Properties.Subject = "ExcelPackage";
            xlPackage.Workbook.Properties.Keywords = "Office Open XML";
            xlPackage.Workbook.Properties.Category = "ExcelPackage";
            xlPackage.Workbook.Properties.Comments = "This file demonstrates how to create an Excel 2007 file from scratch using the Packaging API and Office Open XML";

            // set some extended property values
            xlPackage.Workbook.Properties.Company = "Mars Technology Co.,Ltd.";
            xlPackage.Workbook.Properties.HyperlinkBase = new Uri("http://www.marstech.co.th/");

            // set some custom property values
            ////xlPackage.Workbook.Properties.SetCustomPropertyValue("Checked by", "John Tunnicliffe");
            ////xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147");
            xlPackage.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "ExcelPackage");
        }
        #endregion[PV RV]

        #region[Cheque]
        //private void WriteChequeExcel(string date,string strAmount, string strEmpName, string type)
        //{
        //    if (String.IsNullOrEmpty(strAmount) || String.IsNullOrEmpty(strEmpName)) return;

        //    CheckFolderExist();

        //    FileInfo file = new FileInfo(Path.Combine(path, type+"Template.xlsx"));
        //    if (!file.Exists) return;

        //    using (ExcelPackage xlPackage = new ExcelPackage(file, true))
        //    {
                
        //        ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["DemoCheque"];
        //        if (worksheet != null)
        //        {
        //            worksheet.Cells["N2:O2"].Value = date;
        //            using (ExcelRange r = worksheet.Cells["N2:O2"])
        //            {
        //                r.Merge = true;
        //                r.Style.Font.SetFromFont(new Font("AngsanaUPC", 12, FontStyle.Regular));
        //                r.Style.Font.Color.SetColor(Color.Black);
        //                r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
        //            }
        //            worksheet.Cells["E4:L5"].Value = strEmpName;
        //            using (ExcelRange r = worksheet.Cells["E4:L5"])
        //            {
        //                r.Merge = true;
        //                r.Style.Font.SetFromFont(new Font("AngsanaUPC", 16, FontStyle.Bold));
        //                r.Style.Font.Color.SetColor(Color.Black);
        //                r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
        //            }
        //            if (String.IsNullOrEmpty(strAmount)) strAmount = "0";
        //            worksheet.Cells["M9:O10"].Value = Convert.ToDecimal(strAmount);
        //            using (ExcelRange r = worksheet.Cells["M9:O10"])
        //            {
        //                r.Merge = true;
        //                r.Style.Font.SetFromFont(new Font("AngsanaUPC", 12, FontStyle.Bold));
        //                r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
        //            }
        //        }

        //        WriteFile(type, xlPackage);
        //    }
            
        //}
        #endregion[Cheque]

        protected void postFimTranfers(string paySeq, string bgmSourceSeq, string bgmYear, string recStat)
        {
            if (recStat == "A")
            {
                IBLL_FIM_PaymentCheques paycheque = new B_FIM_PaymentCheques();
                IList<M_FIM_PaymentCheques> paycheq = paycheque.GetFull(paySeq, null);

                IBLL_FIM_Cheques cheque = new B_FIM_Cheques();
                //ใส่ index เป็น 0 เพราะมีรายการเช็คเพียงรายการเดียวตาม Bizz. Rule
                M_FIM_Cheques cheq = cheque.GetInfo(paycheq[0].cheque_seq.ToString());

                string strChequeBookSeq = cheq.cheque_book_seq.ToString();
                string strChequeSeq = cheq.cheque_seq.ToString();
                string strBankAccSeq = cheq.bank_acc_seq.ToString();
                string strChequeNo = cheq.cheque_no.ToString();
                string strPaydate = cheq.pay_date.ToString();
                string strChequedate = cheq.cheque_date.ToString();
                string strReceiver = cheq.receiver;
                string strRemarks = cheq.remarks;
                string strChequeStatus = cheq.cheque_status;
                string strUserID = Session["EmpSeq"].ToString();
                Nullable<DateTime> dtActualPayDate = cheq.actual_pay_date;
                DateTime dtNow = DateTime.Now;
                IBLL_FIM_Transfers transfer = new B_FIM_Transfers();
                IBLL_FIM_MAS_BankAccounts acc = new B_FIM_MAS_BankAccounts();

                if (strChequeStatus == "3" && dtActualPayDate == null)
                {
                    //ไม่สามารถแก้ไขข้อมูลได้ เนื่องจากลงบัญชีแล้ว จึงรีเทิร์นกลับ                
                    return;
                }
                else if ((strChequeStatus == "2" || strChequeStatus == "3") && cheq.pay_date == null)
                {
                    //ไม่สามารถแก้ไขข้อมูลได้ เนื่องจากลงบัญชีแล้ว จึงรีเทิร์นกลับ
                    return;
                }

                if (strChequeStatus == "3")
                {
                    var modelCheque = cheque.GetInfo(strChequeSeq);
                    var modelBankAccount = acc.GetInfo(modelCheque.bank_acc_seq.ToString());

                    if (modelBankAccount.saving_bank_acc_seq != null)
                    {
                        var models = transfer.GetFullFromCheque(strChequeSeq);
                        double SumAmount = main.ConvertNullDouble(paycheque.SumMoney(strChequeSeq)) ?? 0;

                        if (models.Count > 0)
                        {
                            if (models[0].tran_grp_seq == null)
                            {
                                models[0].transfer_date = dtActualPayDate;
                                models[0].updated_by = main.ConvertNullInt(strUserID);
                                models[0].updated_when = dtNow;

                                transfer.Update(models[0]);
                            }
                            else
                            {
                                //ไม่สามารถแก้ไขข้อมูลได้ เนื่องจากลงบัญชีแล้ว จึงรีเทิร์นกลับ
                                return;
                            }
                        }
                        else
                        {
                            var model = new M_FIM_Transfers();

                            model.bgm_source_seq = main.ConvertNullInt(bgmSourceSeq) ?? 0;
                            model.work_area_seq = 0;
                            model.bgm_year = main.ConvertNullInt(bgmYear) ?? 0;
                            model.transfer_type = "3";
                            model.transfer_name = "โอนอัตโนมัติจากการจ่ายเช็ค " + strChequeNo;
                            model.ref_no = "auto transection";
                            model.ref_no_outside = null;
                            model.transfer_date = dtActualPayDate;
                            model.from_bank_acc_seq = modelCheque.bank_acc_seq;
                            model.to_bank_acc_seq = modelBankAccount.saving_bank_acc_seq;
                            model.bank_acc_type_seq = 1;
                            model.amount = SumAmount;
                            model.emp_seq = main.ConvertNullInt(strUserID);
                            model.tran_grp_seq = null;
                            model.record_status = "A";
                            model.created_by = main.ConvertNullInt(strUserID);
                            model.created_when = dtNow;
                            model.cheque_seq = Convert.ToInt32(strChequeSeq);

                            transfer.Insert(model);
                        }
                    }
                }
            }
        }

        #region พงด + ภพ
        private void GetPP30(string seq)
        {
            var ret = pay.GetVatInfo(seq);
            double PrevAmount = pay.GetPrevVatBuySale(ret.month_no, ret.year_no);

            //******PdfSharp******//
            string filename = Server.MapPath(Path.Combine("~/Reports/Templates/", "pp30.pdf"));
            if (!File.Exists(filename))
                return;
            //Open document with convert compatible
            PdfDocument document = PdfReader.Open(filename, PdfDocumentOpenMode.Modify);//PdfReaderCompatible.Open(filename);

            PdfAcroForm form = document.AcroForm;
            // Get all form fields of the whole document
            PdfAcroField.PdfAcroFieldCollection fields = form.Fields;
            double fontSize = 16;
            string fontFam = "TH SarabunPSK";
            XFont myfont = new XFont(fontFam, fontSize, XFontStyle.Regular, new XPdfFontOptions(PdfFontEncoding.Unicode));

            PdfTextField txt = (PdfTextField)fields["txtYear"];
            txt.Font = myfont;
            PdfString strTmp = new PdfString(ret.year_no.ToString());
            txt.Text = ret.year_no.ToString();
            txt.Value = strTmp;
            PdfCheckBoxField c = (PdfCheckBoxField)fields["ckbMonth_" + ret.month_no.ToString()];
            c.Checked = true;

            double value = 0;
            PdfTextField txt1 = (PdfTextField)fields["txt1"];
            value = (ret.sale_amount ?? 0) - (ret.vat_sale_amount ?? 0) + (ret.non_vat_sale_amount ?? 0);
            strTmp = new PdfString(value.ToString("#,##0.00"));
            txt1.Font = myfont;
            txt1.Text = value.ToString("#,##0.00");
            txt1.Value = strTmp;

            value = 0;
            PdfTextField txt2 = (PdfTextField)fields["txt2"];
            strTmp = new PdfString(value.ToString("#,##0.00"));
            txt2.Font = myfont;
            txt2.Text = value.ToString("#,##0.00");
            txt2.Value = strTmp;

            value = ret.non_vat_sale_amount ?? 0;
            PdfTextField txt3 = (PdfTextField)fields["txt3"];
            strTmp = new PdfString(value.ToString("#,##0.00"));
            txt3.Font = myfont;
            txt3.Text = value.ToString("#,##0.00");
            txt3.Value = strTmp;

            double sumSaleAmount = 0;
            PdfTextField txt4 = (PdfTextField)fields["txt4"];
            sumSaleAmount = (ret.sale_amount ?? 0) - (ret.vat_sale_amount ?? 0);
            strTmp = new PdfString(sumSaleAmount.ToString("#,##0.00"));
            txt4.Font = myfont;
            txt4.Text = sumSaleAmount.ToString("#,##0.00");
            txt4.Value = strTmp;

            double taxSale = 0;
            PdfTextField txt5 = (PdfTextField)fields["txt5"];
            taxSale = ret.vat_sale_amount ?? 0;
            strTmp = new PdfString(taxSale.ToString("#,##0.00"));
            txt5.Font = myfont;
            txt5.Text = taxSale.ToString("#,##0.00");
            txt5.Value = strTmp;

            value = 0;
            PdfTextField txt6 = (PdfTextField)fields["txt6"];
            value = (ret.buy_amount ?? 0) - (ret.vat_buy_amount ?? 0);
            strTmp = new PdfString(value.ToString("#,##0.00"));
            txt6.Font = myfont;
            txt6.Text = value.ToString("#,##0.00");
            txt6.Value = strTmp;

            double taxBuy = 0;
            PdfTextField txt7 = (PdfTextField)fields["txt7"];
            taxBuy = ret.vat_buy_amount ?? 0;
            strTmp = new PdfString(taxBuy.ToString("#,##0.00"));
            txt7.Font = myfont;
            txt7.Text = taxBuy.ToString("#,##0.00");
            txt7.Value = strTmp;

            double val8 = 0; double val9 = 0;
            if (taxSale > taxBuy)
            {
                val8 = taxSale - taxBuy;
                PdfTextField txt8 = (PdfTextField)fields["txt8"];
                strTmp = new PdfString(val8.ToString("#,##0.00"));
                txt8.Font = myfont;
                txt8.Text = val8.ToString("#,##0.00");
                txt8.Value = strTmp;
            }
            if (taxSale < taxBuy)
            {
                val9 = taxBuy - taxSale;
                PdfTextField txt9 = (PdfTextField)fields["txt9"];
                strTmp = new PdfString(val9.ToString("#,##0.00"));
                txt9.Font = myfont;
                txt9.Text = val9.ToString("#,##0.00");
                txt9.Value = strTmp;
            }

            double val10 = 0;
            PdfTextField txt10 = (PdfTextField)fields["txt10"];
            val10 = PrevAmount;
            strTmp = new PdfString(val10.ToString("#,##0.00"));
            txt10.Font = myfont;
            txt10.Text = val10.ToString("#,##0.00");
            txt10.Value = strTmp;

            double amountDr = 0;
            amountDr = taxSale - taxBuy + val10;
            double val11 = 0; double val12 = 0;

            if (val8 > val10)
            {
                PdfCheckBoxField c11 = (PdfCheckBoxField)fields["ckb11"];
                c11.Checked = true;
                val11 = amountDr;
                PdfTextField txt11 = (PdfTextField)fields["txt11"];
                strTmp = new PdfString(val11.ToString("#,##0.00"));
                txt11.Font = myfont;
                txt11.Text = val11.ToString("#,##0.00");
                txt11.Value = strTmp;

                PdfTextField txt12 = (PdfTextField)fields["txt12"];
                strTmp = new PdfString((0).ToString("#,##0.00"));
                txt12.Font = myfont;
                txt12.Text = (0).ToString("#,##0.00");
                txt12.Value = strTmp;
            }

            if ((val8 < val10))// || (val8 < val9 + val10))
            {
                PdfTextField txt11 = (PdfTextField)fields["txt11"];
                strTmp = new PdfString((0).ToString("#,##0.00"));
                txt11.Font = myfont;
                txt11.Text = (0).ToString("#,##0.00");
                txt11.Value = strTmp;

                PdfCheckBoxField c12 = (PdfCheckBoxField)fields["ckb12"];
                c12.Checked = true;

                val12 = val9 + val10 - val8;
                PdfTextField txt12 = (PdfTextField)fields["txt12"];
                strTmp = new PdfString(val12.ToString("#,##0.00"));
                txt12.Font = myfont;
                txt12.Text = val12.ToString("#,##0.00");
                txt12.Value = strTmp;
            }

            PdfTextField txt15 = (PdfTextField)fields["txt15"];
            strTmp = new PdfString(val11.ToString("#,##0.00"));
            txt15.Font = myfont;
            txt15.Text = val11.ToString("#,##0.00");
            txt15.Value = strTmp;

            PdfTextField txt16 = (PdfTextField)fields["txt16"];
            strTmp = new PdfString(val12.ToString("#,##0.00"));
            txt16.Font = myfont;
            txt16.Text = val12.ToString("#,##0.00");
            txt16.Value = strTmp;


            if (Session["UserName"] != null)
            {
                PdfTextField txts = (PdfTextField)fields["txtSignature"];
                strTmp = new PdfString(Session["UserName"].ToString());
                txts.Font = myfont;
                txts.Text = Session["UserName"].ToString();
                txts.Value = strTmp;
            }

            string dt = DateTime.Now.ToString("d MMMM yyyy", new System.Globalization.CultureInfo("th-TH"));
            PdfTextField txtd = (PdfTextField)fields["txtDate"];
            strTmp = new PdfString(dt);
            txtd.Font = myfont;
            txtd.Text = dt;
            txtd.Value = strTmp;

            //filename = filename.Substring(0, filename.Length - 4);
            //filename = filename + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".pdf";

            MemoryStream stream = new System.IO.MemoryStream();
            document.Save(stream, false);
            stream.Position = 0;
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.ContentType = "application/pdf";
            Response.AddHeader("content-length", stream.Length.ToString());
            Response.BinaryWrite(stream.ToArray());
            Response.Flush();
            stream.Close();
            Response.End();

        }

        private void GetPND(string PaySeq)
        {
            IList<M_FIM_Payments> lstPND = pay.GetReportPND(PaySeq);

            if (lstPND != null && lstPND.Count > 0)
            {
                string filename = string.Empty; string stype = string.Empty;
                string strType = Request.QueryString["Type"];
                string filetype = strType == "PND2" ? ".pdf" : ".xlsx";
                var type = lstPND[0].contact_type ?? 0;
                if (type == 47)
                {
                    stype = "pnd3" + filetype;
                    filename = Server.MapPath("~/Reports/Templates/" + stype);
                }
                else
                {
                    stype = "pnd53" + filetype;
                    filename = Server.MapPath("~/Reports/Templates/" + stype);
                }

                if (!File.Exists(filename))
                    return;

                string month = lstPND[0].real_pay_date.Value.Month.ToString();
                string year = (lstPND[0].real_pay_date.Value.Year + 543).ToString();
                

                if (strType == "PND2")
                    PdfFile(month, year, lstPND, filename, stype);
                else
                    ExcelFile(lstPND, stype, filename);
            }
        }

        private void ExcelFile(IList<M_FIM_Payments> lstPND, string stype, string filename)
        {
            if (lstPND.Count <= 0) return;
            int rows = 7; //fix start row
            //Dictionary<string, string> _dtD = GetData(lstPND, stype, ref rows);
            double SumAmount = 0;
            double SumTax = 0;
            Dictionary<string, string> dt = new Dictionary<string, string>();
            int n = 0;
            if (stype.ToUpper().StartsWith("PND"))
            {
                dt = new Dictionary<string, string>();
                foreach (M_FIM_Payments vModel in lstPND)
                {
                    if ((vModel.tax ?? 0) >= 0)
                    {
                        n += 1;// seq
                        rows += 1;//start with row 8
                        //double? amt = vModel.amount + vModel.tax;
                        double TaxRate = vModel.tax_rate ?? 0;
                        TaxRate = TaxRate > 1 ? 3 : TaxRate;
                        double Tax = vModel.tax ?? 0;
                        double Amount = TaxRate > 0 ? (Tax * 100) / TaxRate : (vModel.amount ?? 0);
                        SumAmount += Amount;
                        SumTax += Tax;

                        dt.Add(rows.ToString() + ":" + (1).ToString(), n.ToString());
                        dt.Add(rows.ToString() + ":" + (3).ToString(), !String.IsNullOrEmpty(vModel.contact_name) ? vModel.contact_name : "");

                        dt.Add(rows.ToString() + ":" + (26).ToString(), vModel.contact_person_name);
                        //////dt.Add(rows.ToString() + ":" + (5).ToString(), vModel.ref_no);
                        dt.Add(rows.ToString() + ":" + (33).ToString(), vModel.real_pay_date.HasValue ?
                            vModel.real_pay_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                        dt.Add(rows.ToString() + ":" + (37).ToString(), !String.IsNullOrEmpty(vModel.pay_name) ? vModel.pay_name : "");
                        dt.Add(rows.ToString() + ":" + (47).ToString(), TaxRate.ToString("#,##0.00"));
                        dt.Add(rows.ToString() + ":" + (51).ToString(), Amount.ToString("#,##0.00"));//((amt ?? 0) - (vModel.tax ?? 0)).ToString("#,##0.00"));
                        dt.Add(rows.ToString() + ":" + (58).ToString(), Tax.ToString("#,##0.00"));
                        rows += 1;
                        dt.Add(rows.ToString() + ":" + (3).ToString(), vModel.remarks);
                        dt.Add(rows.ToString() + ":" + (26).ToString(), vModel.ref_no);
                    }
                }
            }

            Dictionary<string, string> _dtH = new Dictionary<string, string>();
            //double? tax = lstPND.Sum(o => o.tax);
            //TaxRate > 0 ? (Tax * 100) / TaxRate : (vModel.amount ?? 0)
            //double? amt = lstPND.Sum(o => (o.tax * 100) / o.tax_rate);
            //_dtH.Add("1:1", lstPND.Select(s => s.pay_date.Value).FirstOrDefault().ToString("MMMM yyyy", new System.Globalization.CultureInfo("th-TH")));
            _dtH.Add("1:1", SumAmount.ToString("#,##0.00"));
            _dtH.Add("1:2", SumTax.ToString("#,##0.00"));


            WriteDicToExcel(new Dictionary<string, string>(), _dtH, dt, stype, filename, ref rows, lstPND, SumAmount, SumTax);
        }

        private void WriteDicToExcel(Dictionary<string, string> _dtM, Dictionary<string, string> _dtH
            , Dictionary<string, string> _dtD, string stype, string filename, ref int rows, IList<M_FIM_Payments> lstPND
            , double SumAmount, double SumTax)
        {
            if (_dtD.Count <= 0) return;

            FileInfo newFile = new FileInfo(filename);
            if (!newFile.Exists) return;

            #region[ExcelPackage]
            using (ExcelPackage xlPackage = new ExcelPackage(newFile, true))
            {
                //if (_dtM.Count > 0)
                //    AddWorkSheet(_dtM, "M", ':', xlPackage);

                //if (_dtH.Count > 0)
                //    AddWorkSheet(_dtH, "H", ':', xlPackage);

                if (_dtD.Count > 0)
                    AddWorkSheet(_dtD, "Application Form2", ':', xlPackage, rows, lstPND, SumAmount, SumTax);

                WriteFile(stype.Split('.').ElementAt(0), xlPackage);
            }
            #endregion[ExcelPackage]
        }

        private static void AddWorkSheet(Dictionary<string, string> _dtM, string _sheet, char _char, ExcelPackage xlPackage, int rows, IList<M_FIM_Payments> lstPND
            , double SumAmount, double SumTax)
        {
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[_sheet];
            if (worksheet != null)
            {
                bool IsFooter = true;
                #region[Render Detial]
                foreach (KeyValuePair<string, string> item in _dtM)
                {
                    bool IsMerge = true;

                    string[] _cell = item.Key.Split(_char);
                    #region [previous code]
                    //if (_cell.Length >= 2)
                    //{
                    //    int row = Convert.ToInt32(_cell[0]);
                    //    int col = Convert.ToInt32(_cell[1]);
                    //    //ชื่อ ที่อยู่ = col3-col24, เลขที่ประจำตัวผู้เสียภาษี = col26-col31,  วันเดือนปี = col33-col35,
                    //    //ประเภทเงินได้ = col37-col46, อัตราภาษี = col47-49, จำนวนเงินที่จ่าย = col51-col54, จำนวนเงินที่หัก = col58-col60
                    //    worksheet.Cells[row, col].Value = item.Value;
                    //    worksheet.Cells[row, col].Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                    //    worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    //}
                    #endregion [previous code]
                    if (_cell.Length >= 2)
                    {
                        int row = Convert.ToInt32(_cell[0]);
                        int col = Convert.ToInt32(_cell[1]);
                        worksheet.Cells[row, col].Value = item.Value;
                        worksheet.Cells[row, col].Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                        if (IsMerge)
                        {
                            //ชื่อ ที่อยู่ = col3-col24, เลขที่ประจำตัวผู้เสียภาษี = col26-col31,  วันเดือนปี = col33-col35,
                            //ประเภทเงินได้ = col37-col46, อัตราภาษี = col47-49, จำนวนเงินที่จ่าย = col51-col54, จำนวนเงินที่หัก = col58-col60
                            switch (col)
                            {
                                case 3: worksheet.Cells[row, col, row, col + 21].Merge = true; break;
                                case 26: worksheet.Cells[row, col, row, col + 5].Merge = true; break;
                                case 33: worksheet.Cells[row, col, row, col + 2].Merge = true; break;
                                case 37: worksheet.Cells[row, col, row + 1, col + 9].Merge = true; worksheet.Cells[row, col].Style.WrapText = true; break;
                                case 47: worksheet.Cells[row, col, row, col + 2].Merge = true;
                                    worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                                    break;
                                case 51: worksheet.Cells[row, col, row, col + 3].Merge = true;
                                    worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                                    break;
                                case 58: worksheet.Cells[row, col, row, col + 2].Merge = true;
                                    worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                                    break;
                            }
                        }
                    }
                }
                #endregion[Render Detial]

                #region[Render Footer]
                if (IsFooter)
                {
                    worksheet.Cells["A7:BN7"].Style.Border.Bottom.Style = ExcelBorderStyle.Dotted;
                    worksheet.Cells["A" + rows.ToString() + ":BN" + rows.ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Dotted;

                    string message = "รวมยอดเงินที่ได้และภาษีที่นำส่ง(นำไปรวมกับใบแนบฉบับอื่น(ถ้ามี))";
                    worksheet.Cells["AE" + (rows + 1).ToString() + ":AT" + (rows + 1).ToString()].Value = "รวมยอดเงินที่ได้และภาษีที่นำส่ง(นำไปรวมกับใบแนบฉบับอื่น(ถ้ามี))";
                    using (ExcelRange r = worksheet.Cells["AE" + (rows + 1).ToString() + ":AT" + (rows + 1).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }
                    //Modified on 04/06/57
                    double? tax = SumTax;
                    double? amt = SumAmount;
                    //double? tax = lstPND.Where(x => x.tax_rate != 0).Sum(x => x.tax);
                    //double? amt = lstPND.Where(x => x.tax_rate != 0).Sum(x => (x.pr_amount - x.vat));
                    worksheet.Cells["AY" + (rows + 1).ToString() + ":BB" + (rows + 1).ToString()].Value = amt.Value.ToString("#,##0.00");
                    using (ExcelRange r = worksheet.Cells["AY" + (rows + 1).ToString() + ":BB" + (rows + 1).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    }

                    worksheet.Cells["BF" + (rows + 1).ToString() + ":BH" + (rows + 1).ToString()].Value = tax.Value.ToString("#,##0.00");
                    using (ExcelRange r = worksheet.Cells["BF" + (rows + 1).ToString() + ":BH" + (rows + 1).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    }

                    worksheet.Cells["A" + (rows + 2).ToString() + ":BN" + (rows + 2).ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Double;

                    message = "(ให้กรอกลำดับที่ต่อเนื่องกันไปทุกแผ่น)";
                    worksheet.Cells["A" + (rows + 3).ToString() + ":J" + (rows + 3).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["A" + (rows + 3).ToString() + ":J" + (rows + 3).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }

                    message = "(1)ให้ระบุว่าจ่ายเป็นค่าอะไร เช่น ค่านายหน้า ค่าแห่งกู๊ดวิลล์ ดอกเบี้ยเงินฝาก ดอกเบี้ย ตั๋วเงิน เงินปันผล เงินส่วนแบ่งกำไร ค่าเช่าอาคาร";
                    message += "ค่าสอบบัญชี ค่าออกแบบ ค่าก่อสร้างโรงเรียน ค่าซื้อเครื่องพิมพ์ดีด ค่าซื้อพืชผลทางการเกษตร (ยางพารา มันสำปะหลัง ปอ ข้าว ฯลฯ)";
                    message += "ค่าจ้างทำของ ค่าจ้างโฆษณา รางวัล ส่วนลดหรือประโยชน์ใดๆ เนื่องจากการส่งเสริมการขาย รางวัลในการประกวด การแข่งขัน";
                    message += "การชิงโชค ค่าขนส่งสินค้า ค่าเบี้ยประกันวินาศภัย";

                    worksheet.Cells["A" + (rows + 4).ToString() + ":AE" + (rows + 7).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["A" + (rows + 4).ToString() + ":AE" + (rows + 7).ToString()])
                    {
                        r.Merge = true;
                        r.Style.WrapText = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }

                    message = "(2)เงื่อนไขการหักภาษี ณ ที่จ่าย ให้กรอกดังนี้   หัก ณ ที่จ่าย กรอก 1  ออกภาษีให้ กรอก 2";
                    worksheet.Cells["A" + (rows + 8).ToString() + ":AE" + (rows + 8).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["A" + (rows + 8).ToString() + ":AE" + (rows + 8).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }

                    message = "ประทับตรานิติบุคคล(ถ้ามี)";
                    worksheet.Cells["AG" + (rows + 4).ToString() + ":AK" + (rows + 8).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["AG" + (rows + 4).ToString() + ":AK" + (rows + 8).ToString()])
                    {
                        r.Merge = true;
                        r.Style.WrapText = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        r.Style.Border.Left.Style = ExcelBorderStyle.DashDot;
                        r.Style.Border.Right.Style = ExcelBorderStyle.DashDot;
                    }

                    message = "ลงชื่อ...............................................................ผู้จ่ายเงิน";
                    worksheet.Cells["AO" + (rows + 5).ToString() + ":BD" + (rows + 5).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["AO" + (rows + 5).ToString() + ":BD" + (rows + 5).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    }

                    message = "(...................................................................)";
                    worksheet.Cells["AO" + (rows + 6).ToString() + ":BD" + (rows + 6).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["AO" + (rows + 6).ToString() + ":BD" + (rows + 6).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    }

                    message = "ตำแหน่ง_____________________________";
                    worksheet.Cells["AO" + (rows + 7).ToString() + ":BD" + (rows + 7).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["AO" + (rows + 7).ToString() + ":BD" + (rows + 7).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    }

                    message = DateTime.Now.ToString("dd MMMM yyyy", new System.Globalization.CultureInfo("th-TH"));
                    worksheet.Cells["AO" + (rows + 8).ToString() + ":BD" + (rows + 8).ToString()].Value = message;
                    using (ExcelRange r = worksheet.Cells["AO" + (rows + 8).ToString() + ":BD" + (rows + 8).ToString()])
                    {
                        r.Merge = true;
                        r.Style.Font.SetFromFont(new Font("TH SarabunPSK", 12, FontStyle.Regular));
                        r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    }
                }
                #endregion[Render Footer]
            }
        }

        private void PdfFile(string month, string year, IList<M_FIM_Payments> lstPND, string filename, string stype)
        {
            if (lstPND.Count <= 0)
            {
                //Response.Write("   Not Found Data!");
                //return;
            }
            double? tax = lstPND.Sum(o => o.tax);
            double? amt = lstPND.Sum(o => (o.tax * 100) / o.tax_rate);//lstPND.Sum(o => o.amount);// +tax;
            double cntPages = Math.Ceiling((((double)lstPND.Count + 3) / 15.0));

            #region[PdfDocument]
            //Open document with convert compatible
            PdfDocument document = PdfReader.Open(filename, PdfDocumentOpenMode.Modify);//PdfReaderCompatible.Open(filename);

            PdfAcroForm form = document.AcroForm;
            // Get all form fields of the whole document
            PdfAcroField.PdfAcroFieldCollection fields = form.Fields;
            double fontSize = 16;
            string fontFam = "TH SarabunPSK";
            XFont myfont = new XFont(fontFam, fontSize, XFontStyle.Regular, new XPdfFontOptions(PdfFontEncoding.Unicode));

            PdfTextField txtYear = (PdfTextField)fields["txtYear"];
            txtYear.Font = myfont;
            PdfString strTmp = new PdfString(year);
            txtYear.Text = year;
            txtYear.Value = strTmp;

            PdfCheckBoxField c = (PdfCheckBoxField)fields["ckbMonth_" + month];
            c.Checked = true;

            PdfTextField txtAttachPerson = (PdfTextField)fields["txtAttachPerson"];
            txtAttachPerson.Font = myfont;
            strTmp = new PdfString(lstPND.Count.ToString("#,##0"));
            txtAttachPerson.Text = lstPND.Count.ToString("#,##0");
            txtAttachPerson.Value = strTmp;

            PdfTextField txtAttachSheet = (PdfTextField)fields["txtAttachSheet"];
            txtAttachSheet.Font = myfont;
            strTmp = new PdfString(cntPages.ToString("#,##0"));
            txtAttachSheet.Text = cntPages.ToString("#,##0");
            txtAttachSheet.Value = strTmp;

            PdfTextField txtSumTax1 = (PdfTextField)fields["txtSumTax1"];
            txtSumTax1.Font = myfont;
            strTmp = new PdfString(amt.Value.ToString("#,##0.00"));
            txtSumTax1.Text = amt.Value.ToString("#,##0.00");
            txtSumTax1.Value = strTmp;

            PdfTextField txtSumTax2 = (PdfTextField)fields["txtSumTax2"];
            txtSumTax2.Font = myfont;
            strTmp = new PdfString(tax.Value.ToString("#,##0.00"));
            txtSumTax2.Text = tax.Value.ToString("#,##0.00");
            txtSumTax2.Value = strTmp;

            PdfTextField txtSumTax4 = (PdfTextField)fields["txtSumTax4"];
            txtSumTax4.Font = myfont;
            strTmp = new PdfString(tax.Value.ToString("#,##0.00"));
            txtSumTax4.Text = tax.Value.ToString("#,##0.00");
            txtSumTax4.Value = strTmp;

            if (Session["UserName"] != null)
            {
                PdfTextField txts = (PdfTextField)fields["txtSignature"];
                strTmp = new PdfString(Session["UserName"].ToString());
                txts.Font = myfont;
                txts.Text = Session["UserName"].ToString();
                txts.Value = strTmp;
            }

            if (Session["PosName"] != null)
            {
                PdfTextField txtPosition = (PdfTextField)fields["txtPosition"];
                strTmp = new PdfString(Session["PosName"].ToString());
                txtPosition.Font = myfont;
                txtPosition.Text = Session["PosName"].ToString();
                txtPosition.Value = strTmp;
            }

            string dt = DateTime.Now.ToString("d/MMMM/yyyy", new System.Globalization.CultureInfo("th-TH"));
            string[] arrDt = dt.Split('/');
            if (arrDt.Length >= 3)
            {
                PdfTextField txtDay = (PdfTextField)fields["txtDay"];
                strTmp = new PdfString(arrDt[0].ToString());
                txtDay.Font = myfont;
                txtDay.Text = arrDt[0].ToString();
                txtDay.Value = strTmp;

                PdfTextField txtMonth = (PdfTextField)fields["txtMonth"];
                strTmp = new PdfString(arrDt[1].ToString());
                txtMonth.Font = myfont;
                txtMonth.Text = arrDt[1].ToString();
                txtMonth.Value = strTmp;
            }

            PdfTextField txtYear2 = (PdfTextField)fields["txtYear2"];
            txtYear2.Font = myfont;
            strTmp = new PdfString(year);
            txtYear2.Text = year;
            txtYear2.Value = strTmp;
            #endregion[PdfDocument]

            #region[stream pdf]
            string newfilename = stype.Split('.').ElementAt(0) + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".pdf";

            MemoryStream stream = new System.IO.MemoryStream();
            document.Save(stream, false);
            stream.Position = 0;
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.ContentType = "application/pdf";
            Response.AddHeader("content-length", stream.Length.ToString());
            Response.AddHeader("content-disposition", "inline;filename=" + newfilename);
            Response.BinaryWrite(stream.ToArray());

            stream.Close();
            Response.Flush();
            Response.End();
            #endregion[stream pdf]
        }

        private Dictionary<string, string> GetData(IList<M_FIM_Payments> lstModel, string stype, ref int rows)
        {
            #region[PND]
            Dictionary<string, string> dt = new Dictionary<string, string>();
            int n = 0;
            if (stype.ToUpper().StartsWith("PND"))
            {
                dt = new Dictionary<string, string>();
                foreach (M_FIM_Payments vModel in lstModel)
                {
                    if ((vModel.tax ?? 0) >= 0)
                    {
                        n += 1;// seq
                        rows += 1;//start with row 8
                        //double? amt = vModel.amount + vModel.tax;
                        double TaxRate = vModel.tax_rate ?? 0;
                        TaxRate = TaxRate > 1 ? 3 : TaxRate;
                        double Tax = vModel.tax ?? 0;
                        double Amount = TaxRate > 0 ? (Tax * 100) / TaxRate : (vModel.amount ?? 0);

                        dt.Add(rows.ToString() + ":" + (1).ToString(), n.ToString());
                        dt.Add(rows.ToString() + ":" + (3).ToString(), !String.IsNullOrEmpty(vModel.contact_name) ? vModel.contact_name : "");

                        dt.Add(rows.ToString() + ":" + (26).ToString(), vModel.contact_person_name);
                        //////dt.Add(rows.ToString() + ":" + (5).ToString(), vModel.ref_no);
                        dt.Add(rows.ToString() + ":" + (33).ToString(), vModel.real_pay_date.HasValue ?
                            vModel.real_pay_date.Value.ToString("dd/MM/yyyy", new System.Globalization.CultureInfo("th-TH")) : "");
                        dt.Add(rows.ToString() + ":" + (37).ToString(), !String.IsNullOrEmpty(vModel.pay_name) ? vModel.pay_name : "");
                        dt.Add(rows.ToString() + ":" + (47).ToString(), TaxRate.ToString("#,##0.00"));
                        dt.Add(rows.ToString() + ":" + (51).ToString(), Amount.ToString("#,##0.00"));//((amt ?? 0) - (vModel.tax ?? 0)).ToString("#,##0.00"));
                        dt.Add(rows.ToString() + ":" + (58).ToString(), vModel.tax.HasValue ?
                            vModel.tax.Value.ToString("#,##0.00") : "0.00");
                        rows += 1;
                        dt.Add(rows.ToString() + ":" + (3).ToString(), vModel.remarks);
                        dt.Add(rows.ToString() + ":" + (26).ToString(), vModel.ref_no);
                    }
                }
            }
            return dt;
            #endregion[PND]
        }
        #endregion

        private void GetSalary(string month, string year)
        {
            Salary rpt = new Salary();
            SsMng ssmng = new SsMng();
            B_MessageSend send = new B_MessageSend();

            int nLastStepNo = (main.ConvertNullInt(send.GetLastStepNo("FIM_MONEY_REQUESTS", 9999999999)) ?? 0);
            DataTable dtbApprover1 = send.GetCurrentApprover(ssmng.EmpSeq, null, "FIM_MONEY_REQUESTS", 1);
            DataTable dtbApprover2 = send.GetCurrentApprover(ssmng.EmpSeq, null, "FIM_MONEY_REQUESTS", nLastStepNo);
            M_HRM_PS_Employees empInfo3 = emp.GetInfo(ssmng.EmpSeq);
            M_HRM_PS_Employees empInfo1 = emp.GetInfo(dtbApprover1.Rows[0]["emp_seq"].ToString());
            M_HRM_PS_Employees empInfo2 = emp.GetInfo(dtbApprover2.Rows[0]["emp_seq"].ToString());
            //rpt.SetParameterValue(0, ssmng.UserName);
            //rpt.SetParameterValue(1, empInfo1.emp_name);
            //rpt.SetParameterValue(2, empInfo1.pos_name);
            //rpt.SetParameterValue(3, empInfo2.emp_name);
            //rpt.SetParameterValue(4, empInfo2.pos_name);

            DataTable dtb = report.Salary(month, year);
            DataTable dtb2 = new DataTable();
            dtb2.Columns.Add(new DataColumn("LongDate"));
            dtb2.Columns.Add(new DataColumn("MonthYear"));
            dtb2.Columns.Add(new DataColumn("SumAmount"));
            dtb2.Columns.Add(new DataColumn("SumAmountText"));
            dtb2.Columns.Add(new DataColumn("Detail"));
            dtb2.Columns.Add(new DataColumn("count_emp"));
            dtb2.Columns.Add(new DataColumn("EmpName"));
            dtb2.Columns.Add(new DataColumn("Approver1"));
            dtb2.Columns.Add(new DataColumn("ApproverPos1"));
            dtb2.Columns.Add(new DataColumn("Approver2"));
            dtb2.Columns.Add(new DataColumn("ApproverPos2"));

            if (dtb.Rows.Count > 0)
            {
                string LongDate = main.ConvertNullDateTime(dtb.Rows[0]["pay_date"]).HasValue ? main.ConvertNullDateTime(dtb.Rows[0]["pay_date"]).Value.ToLongDateString() : "";
                string[] arrDate = LongDate.Split(' ');
                string MonthYear = arrDate[1] + " " + arrDate[2];
                double dSumAmount = 0;

                foreach(DataRow dr in dtb.Rows)
                {
                    dSumAmount += main.ConvertNullDouble(dr["Amount"]) ?? 0;
                }

                string SumAmount = dSumAmount.ToString("#,##0.00");
                string SumAmountText = main.MoneyTextThai(SumAmount);

                for (int i = 0; i < dtb.Rows.Count; i++ )
                {
                    DataRow dr = dtb2.NewRow();
                    double dAmount = main.ConvertNullDouble(dtb.Rows[i]["amount"]) ?? 0;

                    dr["LongDate"] = LongDate;
                    dr["MonthYear"] = MonthYear;
                    dr["SumAmount"] = SumAmount;
                    dr["SumAmountText"] = SumAmountText;
                    dr["Detail"] = string.Format("{0} เป็นเงิน {1} บาท ({2}) สั่งจ่ายเช็คในนาม {3} อ้างอิง {4}"
                        , dtb.Rows[i]["payroll_sentpayment_combine_name"].ToString()
                        , dAmount.ToString("#,##0.00")
                        , main.MoneyTextThai(dAmount)
                        , dtb.Rows[i]["NameTh"].ToString()
                        , dtb.Rows[i]["ref_no"].ToString()
                    );
                    dr["count_emp"] = main.ConvertNullInt(dtb.Rows[i]["count_emp"]);
                    dr["EmpName"] = empInfo3.emp_name;
                    dr["Approver1"] = empInfo1.emp_name;
                    dr["ApproverPos1"] = empInfo1.pos_name;
                    dr["Approver2"] = empInfo2.emp_name;
                    dr["ApproverPos2"] = empInfo2.pos_name;

                    dtb2.Rows.Add(dr);
                }
            }

            rpt.SetDataSource(dtb2);
            rpt.ExportToHttpResponse(ExportFormatType.WordForWindows, Response, true, "Salary");
        }

        private M_ICM_DeliveryNote GetDnInfo(int DnId)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            string strSQL;
            DataTable dtb = null;
            M_ICM_DeliveryNote info = new M_ICM_DeliveryNote();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", DnId);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT *"
                        + " FROM IcmDeliveryNote dn"
                        + " WHERE dn.RecordStatus not LIKE @delete_status AND dn.DnId = @seq";


                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    info.icm_dn_ref_no = dtb.Rows[0]["Code"].ToString();
                    info.receive_date = main.ConvertNullDateTime(dtb.Rows[0]["ReceiveDate"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
    }
}